package hotel.discard;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import hotel.common.JDBC;
import hotel.modal.User;

public class UserSql {
	protected static Connection conn = null;
	protected static Statement stmt = null;
	protected static ResultSet rs = null;
	
	// 获取所有用户信息
	public static ArrayList<User> getAll() {
		String sql;
		ArrayList<User> data = new ArrayList<User>();
		try {
			conn = JDBC.getConnection();
			if(conn != null) {
				stmt = conn.createStatement();
				sql = "SELECT UserName, Password, EmployeeID, Admin FROM user";
				rs = stmt.executeQuery(sql);
				String userName, password, strEmployeeId;
				int employeeId, admin = 0;
				while(rs.next()) {
					userName = rs.getString("UserName").trim();
					password = rs.getString("Password").trim();
					strEmployeeId = rs.getString("EmployeeID");
					if(strEmployeeId == null) employeeId = -1;
					else employeeId = Integer.parseInt(strEmployeeId.trim());
					admin = Integer.parseInt(rs.getString("Admin").trim());
					User user = new User(userName, password, employeeId, admin);
					data.add(user);
					
				}
				stmt.close();
				/*
				System.out.println("数据库表Student已有数据"+data.size()+"条");
				for(int i = 0; i < data.size(); i++){
					System.out.println(data.get(i).toString());
				}*/
			}
			JDBC.closeConnection();
		}catch(SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		}finally {
			UserSql.closeAll();
		}
		return data;
	}
	
	// 获取所有用户信息
	public static User get(String userName) {
		String sql;
		User user = null;
		try {
			conn = JDBC.getConnection();
			if (conn != null) {
				stmt = conn.createStatement();
				sql = String.format("SELECT * FROM user where UserName='%s' limit 0,1", userName);
				// System.out.println(sql);
				rs = stmt.executeQuery(sql);
				String password, strEmployeeId;
				int employeeId, admin = 0;
				while (rs.next()) {
					userName = rs.getString("UserName").trim();
					password = rs.getString("Password").trim();
					strEmployeeId = rs.getString("EmployeeID");
					if (strEmployeeId == null)
						employeeId = -1;
					else
						employeeId = Integer.parseInt(strEmployeeId.trim());
					admin = Integer.parseInt(rs.getString("Admin").trim());
					user = new User(userName, password, employeeId, admin);
				}
				stmt.close();
			}
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		} finally {
			UserSql.closeAll();
		}
		return user;
	}
	
	// 更新用户信息
	public static Boolean update(User user) {
		String sql;
		int admin = 0,  ret = 0;
		if(user.isAdmin()) admin = 1;
		try{
			conn = JDBC.getConnection();
			if(conn == null) { return false; }
			stmt = conn.createStatement();
			sql = String.format("update user set Admin=%d where UserName='%s'", admin, user.getUserName());
			//System.out.println(sql);
			ret = stmt.executeUpdate(sql);
			stmt.close();
			JDBC.closeConnection();
		}catch(SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		}finally {
			UserSql.closeAll();
		}
		if(ret == 1) return true;
		else return false;
	}
	
	// 修改用户密码
	public static Boolean changePassword(User user) {
		String sql;
		int ret = 0;
		try{
			conn = JDBC.getConnection();
			if(conn == null) { return false; }
			stmt = conn.createStatement();
			sql = String.format("update user set Password='%s' where UserName='%s'", user.getPassword(), user.getUserName());
			//System.out.println(sql);
			ret = stmt.executeUpdate(sql);
			stmt.close();
			JDBC.closeConnection();
		}catch(SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		}finally {
			UserSql.closeAll();
		}
		if(ret == 1) return true;
		else return false;
	}
	
	// 删除用户信息
	public static Boolean delete(User user) {
		String sql;
		int ret = 0;
		try {
			conn = JDBC.getConnection();
			if (conn == null) {
				return false;
			}
			stmt = conn.createStatement();
			sql = String.format("delete from user where UserName='%s'", user.getUserName());
			//System.out.println(sql);
			ret = stmt.executeUpdate(sql);
			stmt.close();
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		}finally {
			UserSql.closeAll();
		}
		if (ret == 1)
			return true;
		else
			return false;
	}
	
	// 添加用户信息
	public static Boolean add(User user) {
		String sql = null;
		int ret = 0;
		try {
			conn = JDBC.getConnection();
			if (conn == null) {
				return false;
			}
			stmt = conn.createStatement();
			if(user.getEmployeeId() != -1) 
			{
				sql = String.format("insert into user(UserName, Password, EmployeeID, Admin) values('%s','%s','%s',%d)",
					user.getUserName(), user.getPassword(), user.getEmployeeId(), user.isAdmin()?1:0);
			}
			else {
				sql = String.format("insert into user(UserName, Password, Admin) values('%s','%s',%d)",
						user.getUserName(), user.getPassword(), user.isAdmin()?1:0);
			}
			//System.out.println(sql);
			ret = stmt.executeUpdate(sql);
			stmt.close();
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		}finally {
			UserSql.closeAll();
		}
		if (ret == 1)
			return true;
		else
			return false;
	}
	
	private static void closeAll() {
		if (rs != null) try { rs.close(); } catch(Exception e) {}
	    if (stmt != null) try { stmt.close(); } catch(Exception e) {}
	    if (conn != null) try { conn.close(); } catch(Exception e) {}
	}
}
